﻿// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Threading;
using System.Data;

namespace SnapObjects.Data
{
    internal abstract class SqlColumnSchemaProvider : ColumnSchemaProvider
    {
        public SqlColumnSchemaProvider(DataContext dataContext) : base (dataContext)
        {
        }

        public virtual IReadOnlyList<DbColumnInfo> GetColumnSchema(
            string sqlText, IEnumerable<IAdoDbParameter> parameters)
        {
            _dataContext.CurrentConnection.Open();

            IAdoDbDataVisitor dataVisiter = null;

            try
            {
                var adoDbProvider = _dataContext.AdoDbProviderFactory.GetAdoDbProvider();

                parameters = parameters ?? new List<IAdoDbParameter>();

                dataVisiter = adoDbProvider.ExecuteSqlQuery(
                    sqlText, parameters, this.GetCommandBehavior());

                var reader = dataVisiter.ReadData();

                var columnSchemas = this.GetDbColumnInfo(reader);

                var columnSchema = columnSchemas.FirstOrDefault();

                this.ExtraHandleColumnSchema(sqlText, columnSchema);

                return columnSchema;
            }
            finally
            {
                dataVisiter?.Dispose();
                _dataContext.CurrentConnection.Close();
            }
        }

        public async virtual Task<IReadOnlyList<DbColumnInfo>> GetColumnSchemaAsync(
            string sqlText, IEnumerable<IAdoDbParameter> parameters, CancellationToken cancellationToken)
        {
            await _dataContext.CurrentConnection.OpenAsync(cancellationToken);

            IAdoDbDataVisitor dataVisiter = null;

            try
            {
                var adoDbProvider = _dataContext.AdoDbProviderFactory.GetAdoDbProvider();

                parameters = parameters ?? new List<IAdoDbParameter>();

                dataVisiter = await adoDbProvider.ExecuteSqlQueryAsync(
                    sqlText, parameters, this.GetCommandBehavior(), cancellationToken);

                var reader = dataVisiter.ReadData();

                var columnSchemas = this.GetDbColumnInfo(reader);

                var columnSchema = columnSchemas.FirstOrDefault();

                this.ExtraHandleColumnSchema(sqlText, columnSchema);

                return columnSchema;
            }
            finally
            {
                if (dataVisiter != null)
                {
                    await dataVisiter.DisposeAsync();
                }

                await _dataContext.CurrentConnection.CloseAsync();
            }
        }

        protected virtual bool IsExtraHandleNullable => false;

        protected virtual void ExtraHandleColumnSchema(
            string sqlText, IReadOnlyList<DbColumnInfo> dbColumnInfos)
        {
            if (this.IsExtraHandleNullable)
            {
                this.ExtraHandleNullable(sqlText, dbColumnInfos);
            }
        }

        protected void ExtraHandleNullable(string sqlText, IReadOnlyList<DbColumnInfo> dbColumnInfos)
        {
            var column = dbColumnInfos.FirstOrDefault(m => !String.IsNullOrWhiteSpace(m.Table));

            if (column == null)
            {
                return;
            }

            var hasMultiTable = dbColumnInfos.Any(
                m => !String.IsNullOrWhiteSpace(m.Table) && !column.Table.Equals(m.Table));

            if (!hasMultiTable)
            {
                hasMultiTable = dbColumnInfos.Any(
                    m => !String.IsNullOrWhiteSpace(column.TableSchema) &&
                    column.Table.Equals(m.Table) &&
                    !column.TableSchema.Equals(m.TableSchema));

                if (!hasMultiTable)
                {
                    var factory = _dataContext.AdoDbSqlProvider.SqlStatementFactory;

                    var sqlParser = factory.GetSqlParser(SqlStatementType.Select);

                    try
                    {
                        var sqlSelect = (SqlSelectStatement)sqlParser.Parse(sqlText);

                        var index = sqlSelect.From.FindIndex(
                            m => !String.IsNullOrWhiteSpace(m.Text) &&
                            m.Text.ToLower().Contains(column.Table.ToLower()));

                        var lastIndex = sqlSelect.From.FindLastIndex(
                            m => !String.IsNullOrWhiteSpace(m.Text) &&
                            m.Text.ToLower().Contains(column.Table.ToLower()));

                        hasMultiTable = index != lastIndex;
                    }
                    catch
                    { 
                        //TODO...
                    }
                }
            }

            if (!hasMultiTable)
            {
                return;
            }

            foreach (var col in dbColumnInfos)
            {
                col.IsNullable = true;
            }
        }
    }
}
